package com.sr.data;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;

import com.sr.tool.SqlHelper;

public class baseDataService {
	
	//查询项目存在与否
	public srProject CheckProject(srProject srp)
	{
		String sql="select * from base_info where ProjectID=?";
		String parameters[]={srp.getprojectID()+""};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			if(rs.next())
			{
				srp.setprojectID(rs.getString(1));
				srp.setprojectName(rs.getString(2));
				srp.setDepartment(rs.getString(3));
				srp.setBeginDate(rs.getDate(4));
				srp.setEndDate(rs.getDate(5));
				srp.setProjectLeader(rs.getNString(6));
				srp.setMainMembers(rs.getInt(7));
				srp.setFromOrg(rs.getString(8));
				srp.setTypeProject(rs.getString(9));
				srp.setLevelProject(rs.getString(10));	
				srp.setDiscipline(rs.getString(11));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//反向引用,关闭资源
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
			
		}

		return srp;
	}
	
	//得到所有项目基础信息
	public ArrayList<srProject> GetAllBase()
	{
		ArrayList<srProject> al=new ArrayList<srProject>();
		String sql="select * from base_info";
		String parameters[]={};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			while(rs.next())
			{
				srProject srp=new srProject();
				srp.setprojectID(rs.getString(1));
				srp.setprojectName(rs.getString(2));
				srp.setDepartment(rs.getString(3));
				srp.setBeginDate(rs.getDate(4));
				srp.setEndDate(rs.getDate(5));
				srp.setProjectLeader(rs.getString(6));
				srp.setMainMembers(rs.getInt(7));
				srp.setFromOrg(rs.getString(8));
				srp.setTypeProject(rs.getString(9));
				srp.setLevelProject(rs.getString(10));	
				srp.setDiscipline(rs.getString(11));
				al.add(srp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
		}
			
		return al;
	}

	
	//根据ID查询
	public srProject getProjectById(String id)
	{
		srProject srp=new srProject();
		String sql="select * from base_info where ProjectID=?";
		String parameters[]={id};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			if(rs.next())
			{
				srp.setprojectID(rs.getString(1));
				srp.setprojectName(rs.getString(2));
				srp.setDepartment(rs.getString(3));
				srp.setBeginDate(rs.getDate(4));
				srp.setEndDate(rs.getDate(5));
				srp.setProjectLeader(rs.getNString(6));
				srp.setMainMembers(rs.getInt(7));
				srp.setFromOrg(rs.getString(8));
				srp.setTypeProject(rs.getString(9));
				srp.setLevelProject(rs.getString(10));	
				srp.setDiscipline(rs.getString(11));	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//反向引用,关闭资源
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
			
		}
		return srp;
	}
	
	//根据项目名模糊查询
	public ArrayList<srProject> GetProjectByName(String Name)
	{
		ArrayList<srProject> al=new ArrayList<srProject>();
		String sql="select * from base_info where ProjectName like ?";
		String parameters[]={"%"+Name+"%"};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			while(rs.next())
			{
				srProject srp=new srProject();
				srp.setprojectID(rs.getString(1));
				srp.setprojectName(rs.getString(2));
				srp.setDepartment(rs.getString(3));
				srp.setBeginDate(rs.getDate(4));
				srp.setEndDate(rs.getDate(5));
				srp.setProjectLeader(rs.getNString(6));
				srp.setMainMembers(rs.getInt(7));
				srp.setFromOrg(rs.getString(8));
				srp.setTypeProject(rs.getString(9));
				srp.setLevelProject(rs.getString(10));	
				srp.setDiscipline(rs.getString(11));
				al.add(srp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{			
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
		}			
		return al;
	}
	
	public int GetCountOfProjectByID(String pID)
	{
		
		int count=0;
		String sql="select COUNT(*) from base_info where ProjectID=?";
		String parameters[]={pID+""};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			if(rs.next())
			{
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//反向引用,关闭资源
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());			
		}
		return count;
	}
	
	//根据项目名查询项目数
	public int GetCountOfProjectName(String projectName)
	{
		
		int rowCount=0;
		String sql="select COUNT(*) from base_info where ProjectName like ?";
		String parameters[]={projectName+""};
		ResultSet rs=SqlHelper.executeQuery(sql, parameters);
		try {
			while(rs.next())
			{
				rowCount++;
			}
		} catch (SQLException e) {			
			e.printStackTrace();
		}finally{
			//反向引用,关闭资源
			SqlHelper.close(SqlHelper.getCt(), SqlHelper.getPs(), SqlHelper.getRs());
			
		}

		return rowCount;
	}
	
	//修改表的内容
	public boolean UpdateProject(srProject srp)
	{
		boolean b=true;
		String sql="update base_info set ProjectName=?,Department=?,BeginDate=?,EndDate=?,ProjectLeader=?,MainMembers=?,FromOrg=?,TypeProject=?,LevelProject=?,Discipline=? where projectID=?";
		String parameters[]={ srp.getprojectName(),srp.getDepartment(),srp.getDateS1(),
				srp.getDateS2(),srp.getProjectLeader(),srp.getMainMembers()+"",srp.getFromOrg(),
				srp.getTypeProject(),srp.getLevelProject(),srp.getDiscipline(),srp.getprojectID()+""};
		System.out.println(Arrays.toString(parameters));
//		try {			
			int row = SqlHelper.executeUpdate(sql, parameters);			
//		} catch (Exception e) {
			// TODO: handle exception
//			e.printStackTrace();
//			b=false;
//		}	
//			System.out.println(row);
		return row>0?b:false;
	}
	
	//删除
	public  boolean DelProject(String projectID)
	{
		boolean b=true;
		String sql="delete from base_info where projectID=?";
		String parameters[]={projectID};
		try {
			SqlHelper.executeUpdate(sql, parameters);
		} catch (Exception e) {
			e.printStackTrace();
			b=false;
		}
		return b;
	}
	
	//添加记录
	public boolean AddProject(srProject srp)
	{
		boolean b=true;
		String sql="insert into base_info(ProjectID,ProjectName,Department,BeginDate,EndDate,ProjectLeader,MainMembers,FromOrg,TypeProject,LevelProject,Discipline) values(?,?,?,?,?,?,?,?,?,?,?)";//
		String parameters[]={srp.getprojectID()+"",srp.getprojectName()+"",srp.getDepartment()+"",srp.getDateS1()+"",
				srp.getDateS2()+"",srp.getProjectLeader()+"",srp.getMainMembers()+"",srp.getFromOrg()+"",
				srp.getTypeProject()+"",srp.getLevelProject()+"",srp.getDiscipline()};
		try {
			
			SqlHelper.executeUpdate(sql, parameters);
			
		} catch (Exception e) {			
			e.printStackTrace();
			b=false;
		}	
		return b;
	}

}
